/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT: BRIDGE C2
PILOT: SCRAP
DATE: 8/4/2022
8/29/2022 rerun on with corrected next_due variable

DESCRIPTION: Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (this file): 
Code CRC outcomes. 

Outcomes:
	UTD at index: done 
	Type of last screen prior to index: None, FIT/FOBT, Imaging
	Months UTD over observation period
			Determine UTD status for end of month for each month in study
			Summarize months_utd, months in study
	Receipt of screening order while due (Poisson with months after due as offset?)
			start of first period due, first screen after due
			check distribution of time until screened 
	Among patients with order, receipt of order for imaging (Logistic regression)
	Among patients with order, receipt of screen while due(Poisson with months after due as offset)
		  -stratify by imaging/non-imaging order
		

INPUT DATA:
scrap.CRC_patient_covariates
scrap.CRC_status_at_index
scrap.CRC_screen_dates
Scrap.CRC_order_dates

OUTPUT DATA:
SCRAP.CRC_UTD_by_month
SCRAP.CRC_analysis_file


RELATED FILE: 


SECTIONS:
A.  Update scrap.CRC_patient_covariates
		Add status_at_index, 
		Type of last screen prior to index: None, FIT/FOBT, Imaging
		>CRC_analysis
B.UTD status
	Months UTD over observation period
			Determine UTD status for end of month for each month in study>SCRAP.CRC_UTD_by_month
			Summarize months_utd, months_in_study, study_offset
			Add to CRC_analysis

C.Receipt of screening order while due (logistic regression)
	start of first period due, 
	length of obs after due
			-check distribution of time until obs end 
(limit to patients with minimum year after date due at index)
	first order after due (include screen dates for screens captured only in HMT or Surg hx?)
	type of order for first screen: FIT/FOBT, Imaging< Both

/*/*D.Among patients with order, */*/
/*/*	receipt of order/referral for imaging (Logistic regression)*/*/
/*/*	Among patients with order, receipt of screen while due(Poisson with months after due as offset)*/*/
/*/*		  -stratify by imaging/non-imaging order*/*/

/****************************************************/;
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";
libname SCRAP2 "e:\\sasroot\BRIDGE C2\SCRAP\CRC_08012023";
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
/****************************************************/

/****************************************************
A.  Update scrap.CRC_patient_covariates
/****************************************************/
/*		Add status_at_index, */
/*		Type of last screen prior to index: None, FIT/FOBT, Imaging*/
proc sql;
create table analysis as
select c.*,
	s.status_at_index,
	s.date_due_at_index,
	s.ever_due 
from 
	scrap.CRC_patient_covariates c left join 
	scrap.CRC_status_at_index s
	on c.raw_patid=s.raw_patid;

*add any_imaging_prior;
proc sql;
create table any_imaging as
select a.raw_patid,
a.index_visit,
max(s.screen in('FIT','FOBT')) as FIT_FOBT_prior,
max(s.screen in('Flexible Sigmoidoscopy', 'Colonography', 'Colonoscopy')) as Imaging_prior
from analysis a left join scrap.crc_screen_dates s
on a.raw_patid=s.raw_patid and s.screen_date<a.index_visit
group by a.raw_patid, a.index_visit;

proc sql;
create table analysis2 as
select a.*,
max(0,i.FIT_FOBT_prior) as FIT_FOBT_prior,
max(0,i.imaging_prior) as imaging_prior
from analysis a left join any_imaging i
on a.raw_patid=i.raw_patid;



/****************************************************
B.UTD status
/****************************************************/
/*	Months UTD over observation period*/
	/*Determine UTD status for end of month for each month in study>SCRAP.CRC_UTD_by_month*/
*create patient base: one rcord for each report date between index and observation_end;

data patients;
set scrap.CRC_patient_covariates; 
keep raw_patid index_visit observation_end;
run;

data patient_months;
set patients;
format report_date date9.;
do i=1 to 44;
	report_date=intnx('month','01JUL2016'd,i-1,'end');
	if report_date>=index_visit and report_date<=observation_end then output;
end;
run;

proc sql;
create table pat_screens as 
	select p.*,
	s.screen_date,
	s.next_due2
from
patient_months p left join 
	scrap.CRC_screen_dates s on
		p.raw_patid=s.raw_patid
and s.screen_date<=p.report_date;
quit;

proc sql;
create table utd_status as
select raw_patid,
report_date,
max(next_due2) as next_due2 format date9.,
max(next_due2)>report_date as UTD
from pat_screens group by raw_patid, report_date
order by raw_patid, report_date;

data SCRAP.CRC_UTD_by_month;
set utd_status;
run;
options nomprint nosymbolgen nomlogic;			

/*Summarize months_utd, months_in_study, study_offset*/
proc sql;
create table months as 
select raw_patid,
count(distinct report_date) as months_in_study,
log(count(distinct report_date)) as study_offset,
sum(UTD) as months_UTD
from SCRAP.CRC_UTD_by_month group by raw_patid;

/*Add to analysis*/
proc sql;
create table analysis3 as
select a.*, 
m.months_UTD,
m.months_in_study,
m.study_offset
from analysis2 a left join months m
on a.raw_patid=m.raw_patid;


/****************************************************/
/*C.Receipt of screening order while due */
/****************************************************/
/*	length of obs after due*/
/*-check distribution of time from first_due to observation to obs end */
data ever_due;
set analysis3;
where ever_due=1;
format one_year_due date9.;
time_after_due=round(yrdif(date_due_at_index,observation_end, 'age'),0.01);
One_year_due=intnx('year',date_due_at_index,1,'same');
run;


/*check for screens before date_due_at_index*/
proc sql;
create table predue_screens as
select e.raw_patid,
e.date_due_at_index,
e.one_year_due,
e.observation_end,
max(s.next_due2) as new_due_date format date9.
from ever_due e left join 
scrap.crc_screen_dates s
on e.raw_patid=s.raw_patid and
s.screen_date>e.index_visit and
s.screen_date<e.date_due_at_index
group by e.raw_patid,
e.date_due_at_index,
e.one_year_due,
e.observation_end;

proc sql;
create table ever_due2 as
select e.*,
max(p.new_due_date,e.date_due_at_index) as first_due_date format date9.
from ever_due e left join predue_screens p
on e.raw_patid=p.raw_patid;

data ever_due2;
set ever_due2;
time_after_due=round(yrdif(first_due_date,observation_end, 'age'),0.01);
One_year_due=intnx('year',first_due_date,1,'same');
if time_after_due<1 then delete;
run;


data HMTSHX_screens;
set scrap.CRC_screen_dates;
where Lab_re_records=0 and ord_proc_records=0;
run;

proc sql;
create table orders as 
select e.raw_patid,
e.date_due_at_index,
o.order_date,
o.screen as order_screen_type,
s.screen_date as hmtshx_screen_date format date9.,
s.screen as hmtshx_screen_type
from ever_due2 e left join 
scrap.crc_order_dates o 
	on e.raw_patid=o.raw_patid and
	o.order_date between e.first_due_date and
	e.one_year_due
left join 
HMTSHX_screens s 
	on e.raw_patid=s.raw_patid and
	s.screen_date between e.first_due_date and
	e.one_year_due;

data orders;
set orders;
sort_date=order_date;
if sort_date=. then sort_date=hmtshx_screen_date;
run;

proc sort data=orders;
by raw_patid sort_date ;
run;

*frirst screen ordered after due date;
data first_order;
set orders;
by raw_patid;
if first.raw_patid then output;
run;

data first_order;
set first_order;
format order_date2 date9.;
order_date2=order_date;
if sort_date^=. then do;
	if order_date2=. then order_date2=HMTSHX_screen_date;
	imaging_order=order_screen_type ^in(' ','FIT','FOBT');
	imaging_order2=imaging_order;
	if order_date=. then imaging_order2=HMTSHX_screen_type ^in(' ','FIT','FOBT');
end;
run;

proc sql;
create table analysis4 as
select a.*,
e.one_year_due,
e.first_due_date,
f.order_date,
f.imaging_order,
f.order_date2,
f.imaging_order2
from analysis3 a left join ever_due2 e
on a.raw_patid=e.raw_patid
left join first_order f
on a.raw_patid=f.raw_patid;
quit;

/****************************************************/
/*D.Among patients with order, */
/****************************************************/

/*	Among patients with order, receipt of screen while due(Poisson with months after due as offset)*/
proc sql;
create table screens_while_due as
select a.raw_patid,
a.first_due_date,
a.observation_end,
a.imaging_order2,
s.screen_date,
s.screen,
s.screen ^in(' ','FIT','FOBT') as imaging_screen
from analysis4 a left join 
scrap.CRC_screen_dates	s
on a.raw_patid=s.raw_patid
and s.screen_date between a.first_due_date and a.observation_end
where a.order_date2^=.
order by raw_patid, screen_date;


data first_screen;
set screens_while_due;
by raw_patid;
where screen_date^=.;
if first.raw_patid then output;
run;


proc sql;
create table analysis5 as
select a.*,
f.screen_date as first_screen_date format date9.,
f.screen,
f.imaging_screen
from analysis4 a left join first_screen f
on a.raw_patid=f.raw_patid;

/*time to first_screen*/
data analysis5;
set analysis5;
if first_screen_date^=. then do;
	time_to_screen=first_screen_date-first_due_date;
end;
run;


data SCRAP.CRC_analysis;
set analysis5;
if first_due_date^=. then do;
	order_1y=order_date^=.;
	orderb_1y=order_date2^=.;
end;
if order_date2^=. then do;
	if first_screen_date=. then do;
		screen_1yr=0;
		screen_6m=0;
	end;
	if first_screen_date^=. then do;
		screen_1yr=time_to_screen<=366;
		screen_6m=time_to_screen<=(366/2);;
	end;
end;
run;

proc sql;
create table visits as
select p.raw_patid,
p.index_visit,
count(distinct e.encounterid) as visits_first_yr
from SCRAP.CRC_analysis p left join
scrap.scrap_encounters e
on p.raw_patid=e.raw_patid and
p.delivery_site_id=p.delivery_site_id
and e.contact_date between p.index_visit and p.index_visit+365
group by p.raw_patid,
p.index_visit;

proc sql;
create table PCvisits as
select p.raw_patid,
p.index_visit,
count(distinct e.encounterid) as PC_visits_first_yr
from SCRAP.CRC_analysis p left join
scrap.scrap_potential_cohort_PC_enc e
on p.raw_patid=e.raw_patid and
p.delivery_site_id=p.delivery_site_id
and e.contact_date between p.index_visit and p.index_visit+365
group by p.raw_patid,
p.index_visit;

proc sql;
create table analysis as select a.*,
v.visits_first_yr,
pc.pc_visits_first_yr
from SCRAP.CRC_analysis a left join visits v
on a.raw_patid=v.raw_patid
left join PCvisits pc
on a.raw_patid=pc.raw_patid;
quit;

/*finalize form of variables for analysis*/
data SCRAP.CRC_analysis;
set analysis;
Due=status_at_index='Due';
female=sex_r='F';
race_ethnic2=race_ethnic;
if race_ethnic in('Nonhispanic Other',
	'Unknown',' ') then race_ethnic2='Other/Unknown';
FPL_cat2=FPL_cat;
if FPL_cat='<=100' then FPL_cat2='<=200';
insurance_cat=payor_type_research;
if insurance_cat in('Other Public', 'Medicaid') then insurance_cat='Medicaid/Other Public';
/*UTD_imaging_index=due=0 and last_screen_prior in('Colonoscopy','Flexible Sigmoidoscopy');*/
months_offset=log(months_in_study);
run;

